Re: why doesn't an index help my simple query? - Mailing list pgsql-novice

At 10:29 PM -0400 5/30/03, Tom Lane wrote:
>Peter Bierman <bierman@apple.com> writes:
>>  As you can see below, using an index doesn't seem to have any
>>  significant impact on the query speed. Why not?
>
>>    Index Scan Backward using events_time_key on events
>>  (cost=0.00..4556.74 rows=71659 width=20) (actual time=0.84..16495.36
>>  rows=16 loops=1)
>>      Filter: (("time")::timestamp with time zone > (now() -
>>'00:02'::interval))
>
>Hm, why is that shown as a "filter" and not an "index condition"?  And
>why is there an explicit conversion to timestamp with time zone in
>there?  Better tell us about the exact data types involved here ...


I was hoping you'd say 'hm'. :-)

The pg_dump says (reordered with sample data at end)

CREATE TABLE therms (
     sn character(16) NOT NULL,
     tid integer NOT NULL,
     name character varying(100) NOT NULL,
     CONSTRAINT therms_sn CHECK ((length(btrim((sn)::text)) = 16))
);

CREATE TABLE events (
     "time" timestamp without time zone DEFAULT
('now'::text)::timestamp(6) with time zone NOT NULL,
     tid integer NOT NULL,
     "temp" double precision NOT NULL
);

CREATE INDEX events_tid_idx ON events USING btree (tid);

ALTER TABLE ONLY therms
     ADD CONSTRAINT therms_pkey PRIMARY KEY (sn);

ALTER TABLE ONLY therms
     ADD CONSTRAINT therms_tid_key UNIQUE (tid);

ALTER TABLE ONLY events
     ADD CONSTRAINT events_time_key UNIQUE ("time");

ALTER TABLE ONLY events
     ADD CONSTRAINT "$1" FOREIGN KEY (tid) REFERENCES therms(tid) ON
UPDATE CASCADE ON DELETE NO ACTION;

COPY therms (sn, tid, name) FROM stdin;
1037c84800080005        1       Hot Tub
10e6a448000800c6        3       Shed 2
1010bb4800080015        4       Shed 3
105fe1480008006c        6       Outside
1083c24800080063        2       Roof
10d8d948000800b9        5       Shed 1
1047ec480008007a        7       Pool
10cdb448000800f4        8       Shed 4
\.

COPY events ("time", tid, "temp") FROM stdin;
2003-05-06 00:25:52.261602      2       55.17
2003-05-06 00:25:53.462081      4       55.27
2003-05-06 00:25:54.463235      5       55.06
2003-05-06 00:25:55.665572      1       63.16
2003-05-06 00:25:56.666579      3       55.17
2003-05-06 00:26:58.275967      2       55.17
\.


I created these tables using the following SQL:

CREATE TABLE therms
(
  sn      CHAR(16) PRIMARY KEY CHECK (length(trim(sn))=16),
  tid     INTEGER UNIQUE NOT NULL,
  name    VARCHAR(100) NOT NULL
);

CREATE TABLE events
(
  time   TIMESTAMP UNIQUE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  tid    INTEGER   NOT NULL,
                   FOREIGN KEY(tid) REFERENCES therms(tid)
                   ON UPDATE CASCADE,
  temp   FLOAT     NOT NULL
);

CREATE INDEX events_tid_idx ON events (tid);

-pmb